<?php

class Utility_SqlFunctions {

    const STATEMENT_CREATE                  = 'create';
    const STATEMENT_CREATE_TABLE            = 'create_table';
    const STATEMENT_CREATE_VIEW             = 'create_view';
    const STATEMENT_CREATE_TEMPORARY_TABLE  = 'create_temporary_table';
    const STATEMENT_DROP                    = 'drop';
    const STATEMENT_INSERT                  = 'insert';
    const STATEMENT_UPDATE                  = 'update';
    const STATEMENT_DELETE                  = 'delete';
    const STATEMENT_SELECT                  = 'select';
    const STATEMENT_ALTER                   = 'alter';
    const STATEMENT_OTHER                   = 'other';

    static public function getStatementType($sql)
    {

        $sqlClassification = self::STATEMENT_OTHER;

        // the 50 is arbituary - just enough to get the first few characters
        $sqlPart = trim(strtolower(substr(ltrim($sql), 0, 50)));

        $statementPatterns = array(
            self::STATEMENT_INSERT                  => '/^insert/Umi',
            self::STATEMENT_UPDATE                  => '/^update/Umi',
            self::STATEMENT_DELETE                  => '/^delete/Umi',
            self::STATEMENT_SELECT                  => '/^select/Umi',
            self::STATEMENT_CREATE_TEMPORARY_TABLE  => '/^create.*temporary.*table/Umi',
            self::STATEMENT_CREATE_TABLE            => '/^create.*table/Umi',
            self::STATEMENT_CREATE_VIEW             => '/^create.*view/Umi',
            self::STATEMENT_CREATE                  => '/^create/Umi',
            self::STATEMENT_DROP                    => '/^drop/Umi',
            self::STATEMENT_ALTER                   => '/^alter/Umi',
        );

        foreach($statementPatterns as $classification => $pattern) {
            if(preg_match($pattern, $sqlPart)) {
                $sqlClassification = $classification;
                break;
            }
        }

        return $sqlClassification;

    }

    static public function getStatementTypeName($type)
    {
        $name = strtoupper(str_replace('_', ' ', $type));
        return $name;
    }

    static public function quoteValue($value)
    {

        $quotedValue = $value;

        switch (gettype($value)) {

            case 'NULL':
                $quotedValue =  'NULL';
                break;

            case 'boolean':
                $quotedValue = $value ? 'TRUE' : 'FALSE';
                break;

            case 'double':
            case 'integer':
                $quotedValue = $value;
                break;

            case 'string':
            default:

                // this will also do arrays, objects, and resources

                // case: a number disguised as a string: don't need quotes
                if (is_numeric($value)) {
                    $quotedValue = $value;
                }

                // case: a database function name (which must be uppercase)
                else if(preg_match('/^[A-Z_]+\(.*\)$/m', $value)) {
                    $quotedValue = $value;
                }

                // case: a normal string: use the database adapter's quote
                else if(is_string($value)) {
                    $quotedValue = "'" . $value . "'";
                }

                break;

        }

        return $quotedValue;
    }

    static public function generateUpdateStatement($table, array $values, $where = null)
    {
        $sql = 'UPDATE ' . $table;

        $updates = array();
        foreach ($values as $key => $value) {
            $updates[] = $key . ' = ' . self::quoteValue($value);
        }
        if (!empty($updates)) {
            $sql .= ' SET ' . implode(', ', $updates);
        }

        if(!empty($where)) {
            $sql .= ' WHERE ' . $where;
        }

        return $sql;
    }

    static public function generateInsertStatement($table, array $values)
    {
        $insertFields = self::generateInsertStatementFields($values);
        $insertValues = self::generateInsertStatementValues($values);

        $sql = 'INSERT INTO ' . $table . ' ' . $insertFields . ' VALUES ' . $insertValues;

        return $sql;
    }

    static public function generateInsertStatementFields(array $values)
    {
        $fieldNames = array_keys($values);
        
        $sql = '(' . implode(', ', $fieldNames) . ')';

        return $sql;
    }

    static public function generateInsertStatementValues(array $values)
    {
        $fieldValues = array();
        foreach($values as $key => $value) {
            $fieldValues[] = self::quoteValue($value);
        }

        $sql = '(' . implode(', ', $fieldValues) . ')';

        return $sql;
    }

    static public function generateDeleteStatement($table, $where)
    {
        $sql = 'DELETE FROM ' . $table;
        if(!empty($where)) {
            $sql .= ' WHERE ' . $where;
        }

        return $sql;
    }

}